package com.matt.repository.impl;

import com.matt.foundation.DO.CoursePlanNurseryClassesDo;
import com.matt.foundation.DO.NonPrepareCourseClassesDO;
import com.matt.foundation.DO.NonProcessCourseClassesDO;
import com.matt.foundation.DO.course.CoursePlanClassesDO;
import com.matt.foundation.DO.course.NoticeCoursePlanDO;
import com.matt.foundation.DO.course.PrepareStaffDO;
import com.matt.foundation.query.ClassesCoursePlanQuery;
import com.matt.foundation.query.CoursePlanQuery;
import com.matt.foundation.query.NoticeCoursePlanQuery;
import com.matt.foundation.vo.course.WebCoursePlanListVo;
import com.matt.repository.base.BaseNativeSqlRepository;
import com.matt.repository.base.ClassesCoursePlanRepository;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Objects;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2018/3/16
 * *******************************
 */
@Component
public class ClassesPlanRepositoryImpl extends BaseNativeSqlRepository implements ClassesCoursePlanRepository {

    @Override
    public List<CoursePlanNurseryClassesDo> findByClassIdAndCourseDate(String classesId, String beginDate, String endDate) {
        String findSql = String.format("select cc.id as classesCoursePlanId, c.id as coursePlanNurseryId, c.topicId as topicId, c.typeName as typeName, c.courseName as courseName, c.beginTime as beginTime, c.endTime as endTime, cc.beginCourseTeacherId as beginCourseTeacherId, "
                + " c.courseDate as courseDate, cc.prepareStatus as prepareStatus , cc.courseStatus as courseStatus "
                + " from course_plan_classes cc right join  course_plan_topic c on c.id = cc.coursePlanId where cc.classesId = '%s' ", classesId);

        if (StringUtils.isNotBlank(beginDate)) {
            findSql += String.format(" and c.courseDate>='%s'", beginDate);
        }
        if (StringUtils.isNotBlank(endDate)) {
            findSql += String.format(" and c.courseDate<='%s'", endDate);
        }
        findSql += " order by c.courseDate asc, c.beginTime asc";
        List<CoursePlanNurseryClassesDo> objectArrayList = sqlObjectList(findSql, CoursePlanNurseryClassesDo.class);
        return objectArrayList;
    }


    @Override
    public List<NonPrepareCourseClassesDO> findByCourseDateAndPrepareStatusAndCourseStatus(String beginDate, String endDate, Integer prepareStatus, Integer courseStatus, Integer category) {
        String findSql = String.format("select cc.id as coursePlanClassesId, cc.classesId as classesId "
                + " from course_plan_classes cc ,classes c where c.id = cc.classesId and " +
                " cc.courseDate >= '%s' and cc.courseDate <= '%s' and cc.prepareStatus = '%s' and cc.courseStatus = '%s' and c.category ='%s'", beginDate, endDate, prepareStatus, courseStatus, category);
        List<NonPrepareCourseClassesDO> objectArrayList = sqlObjectList(findSql, NonPrepareCourseClassesDO.class);
        return objectArrayList;
    }

    @Override
    public List<NonProcessCourseClassesDO> findByCourseDateAndCourseStatus(String today, Integer courseStatus) {
        String findSql = String.format("select cc.id as coursePlanClassesId, cc.classesId as classesId "
                + " from course_plan_classes cc where cc.courseDate <= '%s' and cc.courseStatus = '%s'", today, courseStatus);
        List<NonProcessCourseClassesDO> objectArrayList = sqlObjectList(findSql, NonProcessCourseClassesDO.class);
        return objectArrayList;
    }


    @Override
    public List<CoursePlanClassesDO> findByConditions(ClassesCoursePlanQuery query) {

    	 String findSql = String.format("select cc.id as classesCoursePlanId,  cc.typeName as typeName, cc.courseName as courseName, cc.beginTime as beginTime, cc.endTime as endTime, cc.beginCourseTeacherId as beginCourseTeacherId, cc.courseType as courseType, "
                 + " cc.courseDate as courseDate, cc.prepareStatus as prepareStatus , cc.courseStatus as courseStatus, cs.name as classesName, cc.category as category "
                 + " from course_plan_classes cc , rel_classes_teacher rel, classes cs  where rel.classesId = cc.classesId and cs.id = cc.classesId  and rel.staffId = '%s' and cc.areaId = '%s' ", query.getStaffId(), query.getAreaId());

         if (Objects.nonNull(query.getCourseCategory())) {
             findSql += String.format(" and cc.category='%s' ", query.getCourseCategory());
         }
         
         if (Objects.nonNull(query.getClassName())) {
         	findSql += String.format(" and cs.name like concat('%s', '%s', '%s') ", "%", query.getClassName(), "%");
         }
         
         if (StringUtils.isNotBlank(query.getCourseName())){
         	findSql += String.format(" and cc.courseName like concat('%s', '%s', '%s') ", "%", query.getCourseName(), "%");
         }

         if (Objects.nonNull(query.getPrepareStatus())) {
             findSql += String.format(" and cc.prepareStatus='%s' ", query.getPrepareStatus());
         }

         if (Objects.nonNull(query.getCourseStatus())) {
             findSql += String.format(" and cc.courseStatus='%s' ", query.getCourseStatus());
         }

         if (StringUtils.isNotBlank(query.getBeginDate())) {
             findSql += String.format(" and cc.courseDate>='%s'", query.getBeginDate());
         }

         if (StringUtils.isNotBlank(query.getEndDate())) {
             findSql += String.format(" and cc.courseDate<='%s'", query.getEndDate());
         }

         findSql += " order by cc.courseDate asc, cc.beginTime asc, cc.endTime asc ";

         if (query.getPageSize() != 0) {
             findSql += String.format("  limit %s, %s ", query.getPageNum() * query.getPageSize(), query.getPageSize());
         }
         List<CoursePlanClassesDO> objectArrayList = sqlObjectList(findSql, CoursePlanClassesDO.class);
         return objectArrayList;
    }

    @Override
    public Long countByConditions(ClassesCoursePlanQuery query) {
    	 String findSql = String.format("select count(cc.id) "
                 + " from course_plan_classes cc ,rel_classes_teacher rel, classes cs, course c  where rel.classesId = cc.classesId and cs.id = cc.classesId  and cc.courseId = c.id and rel.staffId = '%s' and cc.areaId = '%s' ", query.getStaffId(), query.getAreaId());

         if (Objects.nonNull(query.getCourseCategory())) {
             findSql += String.format(" and cc.category='%s' ", query.getCourseCategory());
         }
         
         if (Objects.nonNull(query.getClassName())) {
         	findSql += String.format(" and cs.name like concat('%s', '%s', '%s') ", "%", query.getClassName(), "%");
         }
        
         if (StringUtils.isNotBlank(query.getCourseName())){
         	findSql += String.format(" and cc.courseName like concat('%s', '%s', '%s') ", "%", query.getCourseName(), "%");
         }
        
         if (Objects.nonNull(query.getPrepareStatus())) {
             findSql += String.format(" and cc.prepareStatus='%s' ", query.getPrepareStatus());
         }

         if (Objects.nonNull(query.getCourseStatus())) {
             findSql += String.format(" and cc.courseStatus='%s' ", query.getCourseStatus());
         }

         if (StringUtils.isNotBlank(query.getBeginDate())) {
             findSql += String.format(" and cc.courseDate>='%s'", query.getBeginDate());
         }

         if (StringUtils.isNotBlank(query.getEndDate())) {
             findSql += String.format(" and cc.courseDate<='%s'", query.getEndDate());
         }
//         if (Objects.nonNull(query.getType()) && query.getType().equals("today")) {
//             if (StringUtils.isNotBlank(query.getBeginDate())) {
//                 findSql += "AND DATE_FORMAT(cc.courseDate,'%Y-%m-%d')="     ;
//                 findSql += String.format("'%s'", query.getBeginDate());
//             }
//         }
 //
//         if (Objects.nonNull(query.getType()) && query.getType().equals("week")) {
//             if (StringUtils.isNotBlank(query.getBeginDate())) {
//                 findSql += String.format(" and cc.courseDate>='%s'", query.getBeginDate());
//             }
//             if (StringUtils.isNotBlank(query.getEndDate())) {
//                 findSql += String.format(" and cc.courseDate<='%s'", query.getEndDate());
//             }
//         }


         List objectArrayList = sqlSingleList(findSql);
         return Long.valueOf(objectArrayList.get(0).toString());
    }


    @Override
    public List<CoursePlanClassesDO> findHistoryByCondition(ClassesCoursePlanQuery query) {
        String findSql = String.format("select cc.id as classesCoursePlanId, cc.typeName as typeName, cc.courseName as courseName, cc.beginTime as beginTime, cc.endTime as endTime, cc.beginCourseTeacherId as beginCourseTeacherId, cc.courseType as courseType, c.templateType as templateType, "
                + " cc.courseDate as courseDate, cc.prepareStatus as prepareStatus , cc.courseStatus as courseStatus, cs.name as classesName, cc.category as category "
                + " from course_plan_classes cc, rel_teacher_course_plan_history rel, classes cs ,course c where rel.coursePlanId = cc.id and cs.id = cc.classesId and cc.courseId = c.id and rel.staffId = '%s' and cc.areaId = '%s'  ", query.getStaffId(), query.getAreaId());

        if (Objects.nonNull(query.getCourseCategory())) {
            findSql += String.format(" and cc.category='%s' ", query.getCourseCategory());
        }
        
        if (Objects.nonNull(query.getClassName())) {
        	findSql += String.format(" and cs.name like concat('%s', '%s', '%s') ", "%", query.getClassName(), "%");
        }
        
        if (StringUtils.isNotBlank(query.getCourseName())){
        	findSql += String.format(" and cc.courseName like concat('%s', '%s', '%s') ", "%", query.getCourseName(), "%");
        }
       
        if (StringUtils.isNotBlank(query.getEndDate())) {
            findSql += String.format(" and cc.courseDate<'%s'", query.getEndDate());
        }

        if (Objects.nonNull(query.getPrepareStatus())) {
            findSql += String.format(" and cc.prepareStatus='%s' ", query.getPrepareStatus());
        }


        findSql += " order by cc.courseDate desc, cc.beginTime desc";
        if (query.getPageSize() != 0) {
            findSql += String.format("  limit %s, %s ", query.getPageNum() * query.getPageSize(), query.getPageSize());
        }
        List<CoursePlanClassesDO> objectArrayList = sqlObjectList(findSql, CoursePlanClassesDO.class);
        return objectArrayList;
    }

    @Override
    public Long countHistoryByCondition(ClassesCoursePlanQuery query) {
        String findSql = String.format("select count(cc.id) "
                + "  from course_plan_classes cc , rel_teacher_course_plan_history rel, classes cs ,course c where rel.coursePlanId = cc.id and cs.id = cc.classesId and cc.courseId = c.id and rel.staffId = '%s' and cc.areaId = '%s' ", query.getStaffId(), query.getAreaId());

        if (Objects.nonNull(query.getCourseCategory())) {
            findSql += String.format(" and cc.category='%s' ", query.getCourseCategory());
        }
        
        if (Objects.nonNull(query.getClassName())) {
        	findSql += String.format(" and cs.name like concat('%s', '%s', '%s') ", "%", query.getClassName(), "%");
        }
       
        if (StringUtils.isNotBlank(query.getCourseName())){
        	findSql += String.format(" and cc.courseName like concat('%s', '%s', '%s') ", "%", query.getCourseName(), "%");
        }
        
        if (StringUtils.isNotBlank(query.getEndDate())) {
            findSql += String.format(" and cc.courseDate<'%s'", query.getEndDate());
        }

        if (Objects.nonNull(query.getPrepareStatus())) {
            findSql += String.format(" and cc.prepareStatus='%s' ", query.getPrepareStatus());
        }


        List objectArrayList = sqlSingleList(findSql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public long getEarlyAndNurseryAllCount(CoursePlanQuery query) {
        StringBuffer planSql = new StringBuffer("SELECT count( 1 )  FROM course_plan_classes cp LEFT JOIN teaching_area a ON" +
                " cp.teachingAreaId = a.id  WHERE TRUE and cp.courseStatus = 0 ");

        String statusSql = "";
        String areaSql = "";
        if (Objects.nonNull(query.getPrepareStatus())) {
            statusSql = " and cc.prepareStatus = " + query.getPrepareStatus() + " ";
        }
        if (Objects.nonNull(query.getAreaId())) {
            areaSql = " and cc.areaId = '" + query.getAreaId() + "' ";
        }
        StringBuffer nesSql = new StringBuffer("SELECT count( 1 )   FROM classes c LEFT JOIN rel_classes_teacher rct ON" +
                " c.id = rct.classesid RIGHT JOIN  (SELECT cc.classesId, c.id FROM course_plan_nursery_classes cc RIGHT" +
                " JOIN   course_plan_nursery c ON cc.coursePlanId = c.id RIGHT JOIN course_topic ct ON c.topicId = ct.id where cc.courseStatus = 0 " + statusSql + areaSql + ")  a ON c.id = a.classesId  where True ");

        if (StringUtils.isNotBlank(query.getTeacherId())) {
            planSql.append(String.format(" and cp.teacherId = '%s' ", query.getTeacherId()));
            nesSql.append(String.format(" and rct.staffId = '%s'", query.getTeacherId()));
        }
        if (Objects.nonNull(query.getPrepareStatus())) {
            planSql.append(String.format(" and cp.prepareStatus = %s ", query.getPrepareStatus()));
        }
        if (StringUtils.isNotBlank(query.getAreaId())) {
            planSql.append(String.format(" and cp.areaId = '%s' ", query.getAreaId()));
        }
        String sql = "SELECT (" + planSql.toString() + ") + (" + nesSql + ") AS COUNT";
        Long count = sqlSingleResult(sql);
        return count;
    }

    @Override
    public List<WebCoursePlanListVo> findPrepareByConditions(ClassesCoursePlanQuery query) {
        String findSql = String.format("select cc.id as classesCoursePlanId,  cc.typeName as typeName, cc.courseName as courseName, cc.beginTime as beginTime, cc.endTime as endTime, "
                + " cc.courseDate as courseDate, cc.prepareStatus as prepareStatus , cc.courseStatus as courseStatus, cs.name as classesName, cc.category as category, pc.lastModified as lastModified, pc.lastModifiedId as lastModifiedId, pc.lastModifiedName as lastModifiedName"
                + " from prepare_course pc LEFT JOIN course_plan_classes cc on cc.id = pc.coursePlanClassesId left join  classes cs on cc.classesId = cs.id"
                + " where  cc.areaId = '%s' ", query.getAreaId());

        if (Objects.nonNull(query.getCourseCategory())) {
            findSql += String.format(" and cc.category  = '%s' ", query.getCourseCategory());
        }
        //新增备课老师查询
        if (Objects.nonNull(query.getPrepareTeacherId())) {
            findSql += String.format(" and pc.lastModifiedId  = '%s' ", query.getPrepareTeacherId());
        }
        //新增课程类型查询
        if (Objects.nonNull(query.getTypeName())) {
            findSql += String.format(" and pc.typeName  = '%s' ", query.getTypeName());
        }
        if (Objects.nonNull(query.getCourseCategorys())) {
            findSql += String.format(" and cc.category  in (%s) ", query.getCourseCategorys());
        }
        
        if (StringUtils.isNotBlank(query.getCourseName())){
        	findSql += String.format(" and cc.courseName like concat('%s', '%s', '%s') ", "%", query.getCourseName(), "%");
        }

      /*  if (StringUtils.isNotBlank(query.getBeginDate())) {
            findSql += String.format(" and cc.courseDate>='%s'", query.getBeginDate());
        }*/
        //新增
        if (StringUtils.isNotBlank(query.getBeginDate())) {
            findSql += "AND DATE_FORMAT(cc.courseDate,'%Y-%m-%d')>="     ;
            findSql += String.format("'%s'", query.getBeginDate());
        }
        if (StringUtils.isNotBlank(query.getEndDate())) {
            findSql += "AND DATE_FORMAT(cc.courseDate,'%Y-%m-%d')<="     ;
            findSql += String.format("'%s'", query.getEndDate());
        }
        //新增结束
        
        if (Objects.nonNull(query.getPrepareStatus())) {
            findSql += String.format(" and cc.prepareStatus='%s' ", query.getPrepareStatus());
        }

        if (Objects.nonNull(query.getCourseStatus())) {
            findSql += String.format(" and cc.courseStatus='%s' ", query.getCourseStatus());
        }

        if (Objects.nonNull(query.getClassName())) {
        	findSql += String.format(" and cs.name like concat('%s', '%s', '%s') ", "%", query.getClassName(), "%");
        }
        findSql += " order by pc.lastModified desc , cc.courseDate asc ";

        if (query.getPageSize() != 0) {
            findSql += String.format("  limit %s, %s ", query.getPageNum() * query.getPageSize(), query.getPageSize());
        }
        List<WebCoursePlanListVo> objectArrayList = sqlObjectList(findSql, WebCoursePlanListVo.class);
        return objectArrayList;
    }

    @Override
    public Long countPrepareByConditions(ClassesCoursePlanQuery query) {
        String findSql = String.format("select count(cc.id) "
                + "  from prepare_course pc left join course_plan_classes cc on pc.coursePlanClassesId=cc.id  left join  classes cs on cc.classesId = cs.id where cc.areaId = '%s' ", query.getAreaId());

        if (Objects.nonNull(query.getCourseCategory())) {
            findSql += String.format(" and cc.category='%s' ", query.getCourseCategory());
        }
        
        if (Objects.nonNull(query.getCourseCategorys())) {
            findSql += String.format(" and cc.category  in (%s) ", query.getCourseCategorys());
        }
        
        if (StringUtils.isNotBlank(query.getCourseName())){
        	findSql += String.format(" and cc.courseName like concat('%s', '%s', '%s') ", "%", query.getCourseName(), "%");
        }
        //新增备课老师查询
        if (Objects.nonNull(query.getPrepareTeacherId())) {
            findSql += String.format(" and pc.lastModifiedId  = '%s' ", query.getPrepareTeacherId());
        }
        //新增课程类型查询
        if (Objects.nonNull(query.getTypeName())) {
            findSql += String.format(" and pc.typeName  = '%s' ", query.getTypeName());
        }
        
        //新增
        if (StringUtils.isNotBlank(query.getBeginDate())) {
            findSql += "AND DATE_FORMAT(cc.courseDate,'%Y-%m-%d')>="     ;
            findSql += String.format("'%s'", query.getBeginDate());
        }
        if (StringUtils.isNotBlank(query.getEndDate())) {
            findSql += "AND DATE_FORMAT(cc.courseDate,'%Y-%m-%d')<="     ;
            findSql += String.format("'%s'", query.getEndDate());
        }
       /* if (StringUtils.isNotBlank(query.getBeginDate())) {
            findSql += String.format(" and cc.courseDate>='%s'", query.getBeginDate());
        }*/

        if (Objects.nonNull(query.getPrepareStatus())) {
            findSql += String.format(" and cc.prepareStatus='%s' ", query.getPrepareStatus());
        }

        if (Objects.nonNull(query.getCourseStatus())) {
            findSql += String.format(" and cc.courseStatus='%s' ", query.getCourseStatus());
        }
        
        if (Objects.nonNull(query.getClassName())) {
        	findSql += String.format(" and cs.name like concat('%s', '%s', '%s') ", "%", query.getClassName(), "%");
        }

        List objectArrayList = sqlSingleList(findSql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public List<NoticeCoursePlanDO> findNoticeCoursePlan(NoticeCoursePlanQuery query) {

        String findSql = String.format("select cc.id as coursePlanClassesId,  cc.courseName as courseName,  cc.courseDate as courseDate, c.templateType as templateType, "
                + " cs.id as classesId, cs.name as classesName, cs.areaId as areaId "
                + " from course_plan_classes cc , classes cs, course c where  cs.id = cc.classesId and cc.courseId = c.id");

        if (Objects.nonNull(query.getCategory())) {
            findSql += String.format(" and cc.category='%s' ", query.getCategory());
        }

        if (Objects.nonNull(query.getPrepareStatus())) {
            findSql += String.format(" and cc.prepareStatus='%s' ", query.getPrepareStatus());
        }

        if (StringUtils.isNotBlank(query.getBeginDate())) {
            findSql += String.format(" and cc.courseDate>='%s'", query.getBeginDate());
        }
        if (StringUtils.isNotBlank(query.getEndDate())) {
            findSql += String.format(" and cc.courseDate<='%s'", query.getEndDate());
        }

        findSql += " order by cc.courseDate asc, cc.beginTime asc, cc.endTime asc ";

        List<NoticeCoursePlanDO> objectArrayList = sqlObjectList(findSql, NoticeCoursePlanDO.class);
        return objectArrayList;
    }

    @Override
    public List<CoursePlanClassesDO> findTodayCourseByConditions(ClassesCoursePlanQuery query) {

        String findSql = String.format("select cc.id as classesCoursePlanId,  cc.typeName as typeName,c.typeCode, cc.courseName as courseName, cc.beginTime as beginTime, cc.endTime as endTime, cc.beginCourseTeacherId as beginCourseTeacherId, cc.courseType as courseType, c.templateType as templateType, "
                + " cc.courseDate as courseDate, cc.prepareStatus as prepareStatus , cc.courseStatus as courseStatus, cs.name as classesName, cc.category as category "
                + " from course_plan_classes cc, classes cs,course c,course_template_4 ct  where ct.courseId=c.id and cs.id = cc.classesId AND cc.courseId = c.id and  cc.classesId = '%s' ", query.getClassId());

        if (Objects.nonNull(query.getCourseCategory())) {
            //findSql += String.format(" and cc.category='%s' ", query.getCourseCategory());
        }

        if (Objects.nonNull(query.getPrepareStatus())) {
            findSql += String.format(" and cc.prepareStatus='%s' ", query.getPrepareStatus());
        }

        if (Objects.nonNull(query.getCourseStatus())) {
            findSql += String.format(" and cc.courseStatus='%s' ", query.getCourseStatus());
        }

        if (StringUtils.isNotBlank(query.getBeginDate())) {
            findSql += "AND DATE_FORMAT(cc.courseDate,'%Y-%m-%d')="     ;
            findSql += String.format("'%s'", query.getBeginDate());
        }


        findSql += " order by cc.courseDate asc, cc.beginTime asc, cc.endTime asc ";

        if (query.getPageSize() != 0) {
            findSql += String.format("  limit %s, %s ", query.getPageNum() * query.getPageSize(), query.getPageSize());
        }
        List<CoursePlanClassesDO> objectArrayList = sqlObjectList(findSql, CoursePlanClassesDO.class);
        return objectArrayList;
    }

    @Override
    public List<CoursePlanClassesDO> findWeekCourseByConditions(ClassesCoursePlanQuery query) {

        String findSql = String.format("select cc.id as classesCoursePlanId,  cc.typeName as typeName, c.typeCode,cc.courseName as courseName, cc.beginTime as beginTime, cc.endTime as endTime, cc.beginCourseTeacherId as beginCourseTeacherId, cc.courseType as courseType, c.templateType as templateType, "
                + " cc.courseDate as courseDate, cc.prepareStatus as prepareStatus , cc.courseStatus as courseStatus, cs.name as classesName, cc.category as category "
                + " from course_plan_classes cc, classes cs,course c  where cs.id = cc.classesId AND cc.courseId = c.id and  cc.classesId = '%s' ", query.getClassId());

        if (Objects.nonNull(query.getCourseCategory())) {
            //findSql += String.format(" and cc.category='%s' ", query.getCourseCategory());
        }

        if (Objects.nonNull(query.getPrepareStatus())) {
            findSql += String.format(" and cc.prepareStatus='%s' ", query.getPrepareStatus());
        }

        if (Objects.nonNull(query.getCourseStatus())) {
            findSql += String.format(" and cc.courseStatus='%s' ", query.getCourseStatus());
        }


        if (StringUtils.isNotBlank(query.getBeginDate())) {
            findSql += "AND DATE_FORMAT(cc.courseDate,'%Y-%m-%d')>="     ;
            findSql += String.format("'%s'", query.getBeginDate());
        }
        if (StringUtils.isNotBlank(query.getEndDate())) {
            findSql += "AND DATE_FORMAT(cc.courseDate,'%Y-%m-%d')<="     ;
            findSql += String.format("'%s'", query.getEndDate());
        }


        findSql += " order by cc.courseDate asc, cc.beginTime asc, cc.endTime asc ";

        List<CoursePlanClassesDO> objectArrayList = sqlObjectList(findSql, CoursePlanClassesDO.class);
        return objectArrayList;
    }

    @Override
    public List<PrepareStaffDO> getCoursePlanClassList(ClassesCoursePlanQuery query) {


        String findSql = String.format("SELECT cc.id AS coursePlanClassesId, cc.prepareStatus," +
                "cc.classesId,a.`name` as classesName," + "cc.courseId,courseName " +
                "FROM course_plan_classes cc,classes a WHERE cc.classesId = a.id and cc.areaId = '%s'",  query.getAreaId());

        if (Objects.nonNull(query.getCourseCategory())) {
            findSql += String.format(" and cc.category='%s' ", query.getCourseCategory());
        }

        if (Objects.nonNull(query.getPrepareStatus())) {
            findSql += String.format(" and cc.prepareStatus='%s' ", query.getPrepareStatus());
        }

        if (Objects.nonNull(query.getCourseStatus())) {
            findSql += String.format(" and cc.courseStatus='%s' ", query.getCourseStatus());
        }

        if (Objects.nonNull(query.getType()) && query.getType().equals("today")) {
            if (StringUtils.isNotBlank(query.getBeginDate())) {
                findSql += "AND DATE_FORMAT(cc.courseDate,'%Y-%m-%d')="     ;
                findSql += String.format("'%s'", query.getBeginDate());
            }
        }

        if (Objects.nonNull(query.getType()) && query.getType().equals("week")) {
            if (StringUtils.isNotBlank(query.getBeginDate())) {
                findSql += String.format(" and cc.courseDate>='%s'", query.getBeginDate());
            }
            if (StringUtils.isNotBlank(query.getEndDate())) {
                findSql += String.format(" and cc.courseDate<='%s'", query.getEndDate());
            }
        }



        if (query.getPageSize() != 0) {
            findSql += String.format("  limit %s, %s ", query.getPageNum() * query.getPageSize(), query.getPageSize());
        }
        List<PrepareStaffDO> objectArrayList = sqlObjectList(findSql, PrepareStaffDO.class);
        return objectArrayList;
    }


    @Override
    public List<CoursePlanClassesDO> getMyWeekCourseList(ClassesCoursePlanQuery query) {

        String findSql = String.format("select cc.id as classesCoursePlanId,  cc.typeName as typeName, cc.courseName as courseName, cc.beginTime as beginTime, cc.endTime as endTime, cc.beginCourseTeacherId as beginCourseTeacherId, cc.courseType as courseType, "
                + " cc.courseDate as courseDate, cc.prepareStatus as prepareStatus , cc.courseStatus as courseStatus,  cc.category as category "
                + " from course_plan_classes cc,rel_classes_teacher c WHERE cc.classesId = c.classesId and c.staffId ='%s' ", query.getStaffId());

        if (Objects.nonNull(query.getPrepareStatus())) {
            findSql += String.format(" and cc.prepareStatus='%s' ", query.getPrepareStatus());
        }

        if (Objects.nonNull(query.getCourseStatus())) {
            findSql += String.format(" and cc.courseStatus='%s' ", query.getCourseStatus());
        }


        if (StringUtils.isNotBlank(query.getBeginDate())) {
            findSql += "AND DATE_FORMAT(cc.courseDate,'%Y-%m-%d')>="     ;
            findSql += String.format("'%s'", query.getBeginDate());
        }
        if (StringUtils.isNotBlank(query.getEndDate())) {
            findSql += "AND DATE_FORMAT(cc.courseDate,'%Y-%m-%d')<="     ;
            findSql += String.format("'%s'", query.getEndDate());
        }


        List<CoursePlanClassesDO> objectArrayList = sqlObjectList(findSql, CoursePlanClassesDO.class);
        return objectArrayList;
    }
    
    @Override
    public List<CoursePlanClassesDO> findKXTodayCourseByConditions(ClassesCoursePlanQuery query) {

        String findSql = String.format("select cc.id as classesCoursePlanId,ct.type as type, cc.typeName as typeName,c.typeCode, cc.courseName as courseName, cc.beginTime as beginTime, cc.endTime as endTime, cc.beginCourseTeacherId as beginCourseTeacherId, cc.courseType as courseType, c.templateType as templateType, "
                + " cc.courseDate as courseDate, cc.prepareStatus as prepareStatus , cc.courseStatus as courseStatus, cs.name as classesName, cc.category as category "
                + " from course_plan_classes cc, classes cs,course c,course_template_4 ct  where ct.courseId=c.id and ct.courseId=cc.courseId and cs.id = cc.classesId AND cc.courseId = c.id and  cc.classesId = '%s' ", query.getClassId());

        if (Objects.nonNull(query.getCourseCategory())) {
            //findSql += String.format(" and cc.category='%s' ", query.getCourseCategory());
        }

        if (Objects.nonNull(query.getPrepareStatus())) {
            findSql += String.format(" and cc.prepareStatus='%s' ", query.getPrepareStatus());
        }

        if (Objects.nonNull(query.getCourseStatus())) {
            findSql += String.format(" and cc.courseStatus='%s' ", query.getCourseStatus());
        }

        if (StringUtils.isNotBlank(query.getBeginDate())) {
            findSql += "AND DATE_FORMAT(cc.courseDate,'%Y-%m-%d')="     ;
            findSql += String.format("'%s'", query.getBeginDate());
        }


        findSql += " order by cc.courseDate asc, cc.beginTime asc, cc.endTime asc ";

        if (query.getPageSize() != 0) {
            findSql += String.format("  limit %s, %s ", query.getPageNum() * query.getPageSize(), query.getPageSize());
        }
        List<CoursePlanClassesDO> objectArrayList = sqlObjectList(findSql, CoursePlanClassesDO.class);
        return objectArrayList;
    }

    @Override
    public List<CoursePlanClassesDO> findKXWeekCourseByConditions(ClassesCoursePlanQuery query) {

        String findSql = String.format("select ct.type as type,cc.id as classesCoursePlanId,  cc.typeName as typeName, c.typeCode,cc.courseName as courseName, cc.beginTime as beginTime, cc.endTime as endTime, cc.beginCourseTeacherId as beginCourseTeacherId, cc.courseType as courseType, c.templateType as templateType, "
                + " cc.courseDate as courseDate, cc.prepareStatus as prepareStatus , cc.courseStatus as courseStatus, cs.name as classesName, cc.category as category "
                + " from course_plan_classes cc, classes cs,course c,course_template_4 ct where ct.courseId=c.id and ct.courseId=cc.courseId and cs.id = cc.classesId AND cc.courseId = c.id and  cc.classesId = '%s' ", query.getClassId());

        if (Objects.nonNull(query.getCourseCategory())) {
            //findSql += String.format(" and cc.category='%s' ", query.getCourseCategory());
        }

        if (Objects.nonNull(query.getPrepareStatus())) {
            findSql += String.format(" and cc.prepareStatus='%s' ", query.getPrepareStatus());
        }

        if (Objects.nonNull(query.getCourseStatus())) {
            findSql += String.format(" and cc.courseStatus='%s' ", query.getCourseStatus());
        }


        if (StringUtils.isNotBlank(query.getBeginDate())) {
            findSql += "AND DATE_FORMAT(cc.courseDate,'%Y-%m-%d')>="     ;
            findSql += String.format("'%s'", query.getBeginDate());
        }
        if (StringUtils.isNotBlank(query.getEndDate())) {
            findSql += "AND DATE_FORMAT(cc.courseDate,'%Y-%m-%d')<="     ;
            findSql += String.format("'%s'", query.getEndDate());
        }


        findSql += " order by cc.courseDate asc, cc.beginTime asc, cc.endTime asc ";

        List<CoursePlanClassesDO> objectArrayList = sqlObjectList(findSql, CoursePlanClassesDO.class);
        return objectArrayList;
    }
    
    @Override
    public List<CoursePlanClassesDO> findTodayCourseSelfByConditions(ClassesCoursePlanQuery query) {

        String findSql = String.format("select cc.id as classesCoursePlanId,  cc.typeName as typeName,c.typeCode, cc.courseName as courseName, cc.beginTime as beginTime, cc.endTime as endTime, cc.beginCourseTeacherId as beginCourseTeacherId, cc.courseType as courseType, c.templateType as templateType, "
                + " cc.courseDate as courseDate, cc.prepareStatus as prepareStatus , cc.courseStatus as courseStatus, cs.name as classesName, cc.category as category "
                + " from course_plan_classes cc, classes cs,course c  where  cs.id = cc.classesId AND cc.courseId = c.id and  cc.classesId = '%s' ", query.getClassId());

        if (Objects.nonNull(query.getCourseCategory())) {
            //findSql += String.format(" and cc.category='%s' ", query.getCourseCategory());
        }

        if (Objects.nonNull(query.getPrepareStatus())) {
            findSql += String.format(" and cc.prepareStatus='%s' ", query.getPrepareStatus());
        }

        if (Objects.nonNull(query.getCourseStatus())) {
            findSql += String.format(" and cc.courseStatus='%s' ", query.getCourseStatus());
        }

        if (StringUtils.isNotBlank(query.getBeginDate())) {
            findSql += "AND DATE_FORMAT(cc.courseDate,'%Y-%m-%d')="     ;
            findSql += String.format("'%s'", query.getBeginDate());
        }


        findSql += " order by cc.courseDate asc, cc.beginTime asc, cc.endTime asc ";

        if (query.getPageSize() != 0) {
            findSql += String.format("  limit %s, %s ", query.getPageNum() * query.getPageSize(), query.getPageSize());
        }
        List<CoursePlanClassesDO> objectArrayList = sqlObjectList(findSql, CoursePlanClassesDO.class);
        return objectArrayList;
    }


	@Override
	public List<WebCoursePlanListVo> findKXPrepareByConditions(ClassesCoursePlanQuery query) {
		 String findSql = String.format("select cc.id as classesCoursePlanId,  cc.typeName as typeName,cc.courseType as courseType,ce.typeCode as typeCode, cc.courseName as courseName, cc.beginTime as beginTime, cc.endTime as endTime, "
	                + " cc.courseDate as courseDate, cc.prepareStatus as prepareStatus , cc.courseStatus as courseStatus, cs.name as classesName, cc.category as category, pc.lastModified as lastModified, pc.lastModifiedName as lastModifiedName, pc.lastModifiedId as lastModifiedId, ce.templateType as templateType "
	                + " from prepare_course pc LEFT JOIN course_plan_classes cc on cc.id = pc.coursePlanClassesId left join  classes cs on cc.classesId = cs.id left join course ce on cc.courseId = ce.id "
	                + " where  cc.areaId = '%s' ", query.getAreaId());

	        if (Objects.nonNull(query.getCourseCategory())) {
	            findSql += String.format(" and cc.category  = '%s' ", query.getCourseCategory());
	        }
	        //新增
	        if (Objects.nonNull(query.getPrepareTeacherId())) {
	            findSql += String.format(" and pc.lastModifiedId  = '%s' ", query.getPrepareTeacherId());
	        }
	        //新增结束
	        if (Objects.nonNull(query.getCourseCategorys())) {
	            findSql += String.format(" and cc.category  in (%s) ", query.getCourseCategorys());
	        }
	        
	        if (StringUtils.isNotBlank(query.getCourseName())){
	        	findSql += String.format(" and cc.courseName like concat('%s', '%s', '%s') ", "%", query.getCourseName(), "%");
	        }

	        if (StringUtils.isNotBlank(query.getBeginDate())) {
	            findSql += String.format(" and cc.courseDate>='%s'", query.getBeginDate());
	        }
	        //新增
	        if (StringUtils.isNotBlank(query.getBeginDate())) {
	            findSql += "AND DATE_FORMAT(cc.courseDate,'%Y-%m-%d')>="     ;
	            findSql += String.format("'%s'", query.getBeginDate());
	        }
	        if (StringUtils.isNotBlank(query.getEndDate())) {
	            findSql += "AND DATE_FORMAT(cc.courseDate,'%Y-%m-%d')<="     ;
	            findSql += String.format("'%s'", query.getEndDate());
	        }
	        //新增结束

	        if (Objects.nonNull(query.getPrepareStatus())) {
	            findSql += String.format(" and cc.prepareStatus='%s' ", query.getPrepareStatus());
	        }

	        if (Objects.nonNull(query.getCourseStatus())) {
	            findSql += String.format(" and cc.courseStatus='%s' ", query.getCourseStatus());
	        }

	        if (Objects.nonNull(query.getClassName())) {
	        	findSql += String.format(" and cs.name like concat('%s', '%s', '%s') ", "%", query.getClassName(), "%");
	        }
	        findSql += " order by pc.lastModified desc , cc.courseDate asc ";

	      /*  if (query.getPageSize() != 0) {
	            findSql += String.format("  limit %s, %s ", query.getPageNum() * query.getPageSize(), query.getPageSize());
	        }*/
	        List<WebCoursePlanListVo> objectArrayList = sqlObjectList(findSql, WebCoursePlanListVo.class);
	        return objectArrayList;
	}


	@Override
	public Long countKXPrepareByConditions(ClassesCoursePlanQuery query) {
		 String findSql = String.format("select count(cc.id) "
	                + "  from prepare_course pc left join course_plan_classes cc on pc.coursePlanClassesId=cc.id  left join  classes cs on cc.classesId = cs.id where cc.areaId = '%s' ", query.getAreaId());

	        if (Objects.nonNull(query.getCourseCategory())) {
	            findSql += String.format(" and cc.category='%s' ", query.getCourseCategory());
	        }
	        
	        if (Objects.nonNull(query.getCourseCategorys())) {
	            findSql += String.format(" and cc.category  in (%s) ", query.getCourseCategorys());
	        }
	        
	        if (StringUtils.isNotBlank(query.getCourseName())){
	        	findSql += String.format(" and cc.courseName like concat('%s', '%s', '%s') ", "%", query.getCourseName(), "%");
	        }

	        if (StringUtils.isNotBlank(query.getBeginDate())) {
	            findSql += String.format(" and cc.courseDate>='%s'", query.getBeginDate());
	        }

	        if (Objects.nonNull(query.getPrepareStatus())) {
	            findSql += String.format(" and cc.prepareStatus='%s' ", query.getPrepareStatus());
	        }

	        if (Objects.nonNull(query.getCourseStatus())) {
	            findSql += String.format(" and cc.courseStatus='%s' ", query.getCourseStatus());
	        }
	        
	        if (Objects.nonNull(query.getClassName())) {
	        	findSql += String.format(" and cs.name like concat('%s', '%s', '%s') ", "%", query.getClassName(), "%");
	        }

	        List objectArrayList = sqlSingleList(findSql);
	        return Long.valueOf(objectArrayList.get(0).toString());
	}


	
}
